package com.linuxgt.android.dbapp.service;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.linuxgt.android.dbapp.domain.User;

import java.util.ArrayList;
import java.util.List;

/**
 * Test表服务类
 * <p>
 * Created by liuteng on 2017/5/17.
 */

public class UserService {

    private DBOpenHelper dbOpenHelper;
    private SQLiteDatabase sqLiteDatabaseW;
    private SQLiteDatabase sqLiteDatabaseR;

    /**
     * 构造器
     *
     * @param context
     */
    public UserService(Context context) {
        dbOpenHelper = new DBOpenHelper(context);
        sqLiteDatabaseW = dbOpenHelper.getWritableDatabase();
        sqLiteDatabaseR = dbOpenHelper.getReadableDatabase();
    }

    /**
     * 保存记录
     *
     * @param user
     */
    public void save(User user) {
        if (null == user.getId()) {
            sqLiteDatabaseW.execSQL("insert into user(name,age) values (?,?)", new Object[]{user.getName(), user.getAge()});
        } else {
            sqLiteDatabaseW.execSQL("insert into user(id,name,age) values (?,?,?)", new Object[]{user.getId(), user.getName(), user.getAge()});
        }
    }

    /**
     * 删除记录
     *
     * @param id
     */
    public void delete(Integer id) {
        sqLiteDatabaseW.execSQL("delete from user where id=?", new Object[]{id});
    }

    /**
     * 根据id查询记录
     *
     * @param id
     * @return
     */
    public User query(Integer id) {
        Cursor cursor = sqLiteDatabaseR.rawQuery("select * from user where id=?", new String[]{String.valueOf(id)});
        if (cursor.moveToFirst()) {
            String name = cursor.getString(cursor.getColumnIndex("name"));
            int age = cursor.getInt(cursor.getColumnIndex("age"));
            int uid = cursor.getInt(cursor.getColumnIndex("id"));
            return new User(uid, name, age);
        }
        cursor.close();
        return null;
    }

    /**
     * 分页获取记录数
     *
     * @param pageNum
     * @param pageSize
     * @return
     */
    public List<User> getPageData(int pageNum, int pageSize) {
        List<User> testList = new ArrayList<>();
        Cursor cursor = sqLiteDatabaseR.rawQuery("select * from user limit ?,?", new String[]{String.valueOf(pageNum * pageSize), String.valueOf(pageSize)});
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            int age = cursor.getInt(cursor.getColumnIndex("age"));
            testList.add(new User(id, name, age));
        }
        cursor.close();
        return testList;
    }

    /**
     * 更新记录
     *
     * @param user
     */
    public void update(User user) {
        sqLiteDatabaseW.execSQL("update user set name=?,age=? where id=?", new Object[]{user.getName(), user.getAge(), user.getId()});
    }

    /**
     * 获取总数
     *
     * @return
     */
    public long getCount() {
        Cursor cursor = sqLiteDatabaseR.rawQuery("select count(1) from user", null);
        cursor.moveToFirst();
        long count = cursor.getLong(0);
        cursor.close();
        return count;
    }

    /**
     * 转账
     */
    public void payment(Integer from, Integer to, Integer amount) {
        sqLiteDatabaseW.beginTransaction();
        try {
            sqLiteDatabaseW.execSQL("update user set amount = amount - " + amount + " where id=" + from);
            sqLiteDatabaseW.execSQL("update user set amount = amount + " + amount + " where id=" + to);
            sqLiteDatabaseW.setTransactionSuccessful();
        } finally {
            sqLiteDatabaseW.endTransaction();
        }
    }
}
